import pandas as pd
import re

userfile = pd.read_excel(r"C:\Users\Administrator\Desktop\20191116_名字.xlsx", sheet_name='Sheet1')

userfile['username'] = userfile['username'].apply(lambda x: str(x))

# \u4e00-\u9fa5代表汉字,如果中文,数字,英文都可以的话\u4e00-\u9fa5放在最后
#   ^  的意思是匹配开头和取反.不能加
# '[a-zA-Z0-9\u4e00-\u9fa5]+ ' 或者 '[\u4e00-\u9fa5a-zA-Z0-9]' 都是可以的

# userfile['username'] = userfile['username'].apply(
#     lambda x: ''.join(re.findall(r'[a-zA-Z0-9\u4e00-\u9fa5]+', x, re.S))
# )

userfile['username'] = userfile['username'].apply(
    lambda x: ''.join(re.findall(r'[^\u4e00-\u9fa5a-zA-Z0-9]', x, re.S))
)

userfile.to_excel(r"C:\Users\Administrator\Desktop\20191116_NEW.xlsx", index=False)

print(type(userfile))

# x = '%%圈子qa%$z%圈子12'
# # x='圈子qaz12'
# aaa = re.findall(r'[a-zA-Z0-9\u4e00-\u9fa5]+', x, re.S)
#
# for a in aaa:
#     print(a)
#
# bbb = ''.join(aaa)
# print(bbb)
#
# # # x='%%1这是测试用例'
# # print(re.findall(r'[\u4e00-\u9fa5]+', x))
